﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Windows;
using System.Windows.Controls;
using JiaBao.Core;
using JiaBao.Services.CustomerService;
using JiaBao.Services.LogService;

namespace JiaBao.WPF.Customer
{
    /// <summary>
    /// p_Customer_Excel.xaml 的交互逻辑
    /// </summary>
    public partial class p_Customer_Excel : Page
    {
        /// <summary>
        /// 必须包含字段
        /// </summary>
        private string mustcol = "|姓名|手机|证件号码|";


        private readonly ICustomerService _customerService;

        public p_Customer_Excel(ICustomerService customerService)
        {
            InitializeComponent();
            try
            {
                _customerService = customerService;
                lab_Tip.Content = "上传文件必须包含字段: " + mustcol.Substring(1, mustcol.Length - 2);

            }
            catch (Exception ex)
            {
                Log.SaveLog(ex);
                MessageBox.Show(ex.Message);
            }
        }


        /// <summary>
        /// 选择文件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btn_Path_Click(object sender, RoutedEventArgs e)
        {

            try
            {
                var p = CommonHelper.OpenFileDialog("Excel|*.xls");
                if (null != p)
                    txt_Path.Text = p;
            }
            catch (Exception ex)
            {
                Log.SaveLog(ex);
                MessageBox.Show(ex.Message);
            }

        }

        /// <summary>
        /// 导入
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btn_Update_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                //检测文件路径
                var filePath = txt_Path.Text.Trim();
                if (!File.Exists(filePath))
                    return;
                //获取表列
                var filds = _customerService.GetFields();

                Dictionary<string, int> excelFilds = new Dictionary<string, int>();
                HSSFWorkbook wk = null;
                using (FileStream fs = File.OpenRead(filePath))   //打开myxls.xls文件 
                    wk = new HSSFWorkbook(fs);  //把xls文件中的数据写入wk中

                ISheet sheet = wk.GetSheetAt(0);   //读取当1sheet

                #region 读取表头

                //读取第一行 表头
                var row1 = sheet.GetRow(0);
                for (int i = 0; i < row1.Count(); i++)
                {
                    var s = row1.Cells[i].ToString().Trim();
                    if (string.IsNullOrEmpty(s))
                        continue;

                    //是否有必要标识
                    if (mustcol.Contains(s))
                        mustcol = mustcol.Replace("|" + s + "|", "|");

                    //只保存数据库字段标识
                    if (filds.Contains(s))
                        excelFilds.Add(s, i);   //保存列序号名称

                }

                //检测标识字段
                if (mustcol != "|")
                {
                    MessageBox.Show("缺少必要字段");
                    return;
                }

                var cols = string.Empty;
                foreach (var item in excelFilds)
                    cols += "|" + item.Key;

                if (MessageBox.Show("导入字段包含 " + cols.Substring(1), "提示", MessageBoxButton.YesNo) == MessageBoxResult.No) return;

                #endregion

                //获取客户表
                var customers = _customerService.GetAllCustomers();

                //重复数据样式
                ICellStyle style = wk.CreateCellStyle();
                style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index;
                style.FillPattern = FillPattern.SolidForeground;

                #region 拼接语句

                string sql = string.Empty, col = string.Empty, val = string.Empty;
                //遍历 保存的字段标识
                foreach (KeyValuePair<string, int> item in excelFilds)
                {
                    col += "," + item.Key;
                    val += "," + "@val" + item.Value;
                }

                if (excelFilds.ContainsKey("客户来源"))
                {
                    col += ",客户标识";
                    val += ",@bs";
                }
                else
                {
                    col += ",客户标识,客户来源";
                    val += ",@bs,@ly";
                }

                col = col.Substring(1);
                val = val.Substring(1);
                sql = string.Format("insert into Customer_Z ({0}) values ({1})", col, val);

                #endregion

                #region 循环写入数据库

                for (int i = 1; i <= sheet.LastRowNum; i++)  //LastRowNum 是当前表的总行数
                {
                    List<SqlParameter> parameter = new List<SqlParameter>();

                    IRow row = sheet.GetRow(i);  //读取当前行数据

                    #region 检测

                    if (null == row)//空行
                    {
                        row.RowStyle = style;   //行变色
                        continue;
                    }


                    ICell zj = row.GetCell(excelFilds["证件号码"]);
                    ICell sj = row.GetCell(excelFilds["手机"]);

                    if (null == zj && null == sj)//手机 证件 空
                    {
                        row.RowStyle = style;   //行变色
                        continue;
                    }
                    if (null == zj)//证件 空
                    {
                        var sjCount = customers.AsEnumerable().Where(d => d.Field<string>("手机") == sj.ToString()).Count();
                        if (0 < sjCount)//重复
                        {
                            row.RowStyle = style;   //行变色
                            continue;
                        }

                    }
                    else if (null == sj)//手机空
                    {
                        var zjCount = customers.AsEnumerable().Where(d => d.Field<string>("证件号码") == zj.ToString()).Count();
                        if (0 < zjCount)//重复
                        {
                            row.RowStyle = style;   //行变色
                            continue;
                        }
                    }


                    #endregion

                    //遍历 保存的字段标识
                    foreach (KeyValuePair<string, int> item in excelFilds)
                    {
                        ICell cell = row.GetCell(item.Value);
                        parameter.Add(new SqlParameter("val" + item.Value, null == cell ? "" : cell.ToString()));
                    }

                    parameter.Add(new SqlParameter("bs", Guid.NewGuid().ToString("N")));

                    if (!excelFilds.ContainsKey("客户来源"))
                        parameter.Add(new SqlParameter("ly", "外部导入"));

                    _customerService.ExecuteNonQuery(sql, parameter.ToArray());
                }

                #endregion

                using (FileStream fileStream = File.Open(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
                {
                    wk.Write(fileStream);
                    fileStream.Close();
                }

                MessageBox.Show("导入完成,请产看原Excel标记");
            }
            catch (Exception ex)
            {
                Log.SaveLog(ex);
                MessageBox.Show(ex.Message);
            }
        }
    }
}
